{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2e1fad64-93dd-4220-ae92-2cc4f213d1ac",
   "metadata": {},
   "source": [
    "# Setting up the DuckDB Python Client\n",
    "\n",
    "This notebook contains the code examples from chapter 7 of *Getting Started with DuckDB*."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ee03308-f05e-4b47-997f-89086393e698",
   "metadata": {},
   "source": [
    "## Technical requirements\n",
    "\n",
    "In order to run the examples in this notebook, you'll need to install the Python dependencies for this project. You can do this by running the following command in your terminal when in the root directory of the project. Note that ideally this should be using a Python virtual environment for this project.\n",
    "\n",
    "    pip install -r requirements.txt\n",
    "\n",
    "For complete instructions on how to set up your environment for working through the examples, please consult the *Technical requirements* section of this chapter in the book."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a58462d2-8180-40c7-8c26-909cd1aaf1f9",
   "metadata": {},
   "source": [
    "## Connecting to DuckDB in Python"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "644a5fd1-b432-473e-bf0d-668052ccf5e5",
   "metadata": {},
   "source": [
    "### The default in-memory database "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5771650-81f1-4fd9-b282-c1c09fc3f6a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb \n",
    "\n",
    "duckdb.sql(\"SELECT 'duck' AS animal, 'quack!' AS greeting\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dec17b91-e7e6-46c8-83a5-1f0a32f24c68",
   "metadata": {},
   "source": [
    "### Managing database connections explicitly "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee40e10a-0886-4d5c-bed9-8ae07f7bf5db",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = duckdb.connect()\n",
    "\n",
    "conn.sql(\n",
    "    \"\"\"\n",
    "    CREATE TABLE hello AS\n",
    "    SELECT 'pato' AS animal, 'cuac!' AS greeting\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "conn.sql(\"SELECT * FROM hello\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37136625-03f3-4b80-bfbe-0f29c72b14cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = duckdb.connect(database=\":memory:\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ea23110-278b-4be9-92a7-728ab3702754",
   "metadata": {},
   "outputs": [],
   "source": [
    "custom_conn = duckdb.connect(\n",
    "    config={\n",
    "        \"memory_limit\": \"10GB\", \n",
    "        \"threads\": 1\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "526feb6c-3a01-410e-a92f-918c70efa631",
   "metadata": {},
   "source": [
    "### Connections to persistent-storage databases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b62d7cd0-0020-4f91-94ba-f728ebf36e09",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = duckdb.connect(database=\"quack.duckdb\")\n",
    "\n",
    "conn.sql(\n",
    "    \"\"\"\n",
    "    CREATE OR REPLACE TABLE hello AS\n",
    "    SELECT 'ente' AS animal, 'quak!' AS greeting\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f84e0a1-dd3f-4e7c-b226-dcc0e1ad0cb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "! duckdb quack.duckdb -c 'SELECT * FROM hello'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01738d93-7da9-43fd-bdef-5776ad797812",
   "metadata": {},
   "source": [
    "### Closing database connections "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "110bf38b-455a-477e-977e-123050b434bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"INSERT INTO hello VALUES ('Labradorius', 'quack!')\" \n",
    "\n",
    "with duckdb.connect(database=\"quack.duckdb\") as conn:\n",
    "    conn.sql(sql) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb1f24a1-7192-428a-a08d-97e59a3ee25f",
   "metadata": {},
   "source": [
    "### Sharing disk-based databases between processes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3c74f1a-04ec-453c-8ed6-ac9f7c1e2c7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = duckdb.connect(database=\"quack.duckdb\", read_only=True)\n",
    "# query the database in here...\n",
    "conn.close() "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ad469d9-c6e4-4fe9-89e2-ddcc499c2239",
   "metadata": {},
   "source": [
    "### Installing and loading extensions "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88d0b461-91a0-4ed4-a284-b07c66d29070",
   "metadata": {},
   "outputs": [],
   "source": [
    "duckdb.install_extension(\"spatial\")  \n",
    "\n",
    "duckdb.load_extension(\"spatial\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a46eb0a-3b6f-4eae-86d9-7a109ac8e354",
   "metadata": {},
   "outputs": [],
   "source": [
    "duckdb.sql(\n",
    "    \"\"\"\n",
    "    SELECT *\n",
    "    FROM duckdb_extensions()\n",
    "    WHERE loaded = true\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "564cd324-7b2d-4407-8534-b4946db2b46e",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = duckdb.connect()\n",
    "conn.sql(\n",
    "    \"\"\"\n",
    "    SELECT *\n",
    "    FROM duckdb_extensions()\n",
    "    WHERE loaded = true\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "350a6684-239c-4d56-bf9b-e873048bdfcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.install_extension(\"spatial\")  \n",
    "\n",
    "conn.load_extension(\"spatial\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9dc4031a-66be-4f55-ab62-78b34f94c0e1",
   "metadata": {},
   "source": [
    "## Summary"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
